package org.ielts.excel;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.StringReader;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.ielts.configuration.ConfigurationClass;
import org.ielts.domain.LearnStatus;
import org.ielts.domain.Sex;
import org.ielts.domain.base.Course;
import org.ielts.domain.base.SignCourse;
import org.ielts.domain.base.Student;
import org.ielts.domain.log.AppointConsultorLog;
import org.ielts.domain.log.AppointTutorLog;
import org.ielts.domain.log.FirstRecordLog;
import org.ielts.domain.log.FreezeCourseLog;
import org.ielts.domain.log.OrderLessonLog;
import org.ielts.domain.log.SchoolLog;
import org.ielts.domain.security.User;
import org.ielts.repository.AppointConsultorLogRepository;
import org.ielts.repository.AppointTutorLogRepository;
import org.ielts.repository.FirstRecordLogRepository;
import org.ielts.repository.FreezeCourseLogRepository;
import org.ielts.repository.OrderLessonLogRepository;
import org.ielts.repository.StudentRepository;
import org.ielts.repository.security.UserRepository;
import org.ielts.service.ConsultantService;
import org.ielts.service.CourseService;
import org.ielts.service.FrontService;
import org.ielts.service.OrderLessonLogService;
import org.ielts.service.OrderLessonService;
import org.ielts.service.SchoolService;
import org.ielts.service.SignCourseService;
import org.ielts.service.security.UserService;
import org.ielts.util.MapListWapper;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

import com.yifeng.dao.template.JdbcTemplateHelper;
import com.yifeng.dto.DtoTable;
import com.yifeng.dto.IDtoRow;
import com.yifeng.poi.CellReader;
import com.yifeng.poi.ExcelUtils;
import com.yifeng.profile.Profile;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { ConfigurationClass.class })
public class ExcelImport {

	MapListWapper<String, User> ccsm;
	MapListWapper<String, User> tutorsm;
	MapListWapper<String, Course> coursesm;

	@Before
	public void before() {

		ccsm = userService.findNameIdMapMatchRoleName("课程顾问");
		tutorsm = userService.findNameIdMapMatchRoleName("学习顾问");

		coursesm = courseService.findNameIdMap();
	}

	private User findTutorByName(String tutorName) {
		return tutorsm.getMap().get(tutorName.trim().toLowerCase());
	}

	private User findCCByName(String ccName) {
		return ccsm.getMap().get(ccName.trim().toLowerCase());
	}

	private Course findCourseByName(String courseName) {
		return coursesm.getMap().get(courseName.trim());
	}

	@Autowired
	private UserService userService;

	@Autowired
	ConsultantService consultantService;

	@Autowired
	CourseService courseService;

	@Autowired
	FrontService frontService;

	@Autowired
	OrderLessonService orderLessonService;

	@Autowired
	SchoolService schoolService;

	@Autowired
	SignCourseService signCourseService;

	Logger logger = LoggerFactory.getLogger("excelError");

	Date upper = org.apache.commons.lang.time.DateUtils.addYears(new Date(), 10);

	private Foo parse(String time, String day, int rowNum, Date signDate) {

		try {
			int year = 2000;
			int month;
			int d;

			String[] days = StringUtils.splitByCharacterType(day);
			if (days.length < 3) {
				logger.debug(rowNum + "行，无法处理的日期格式" + day);
				return null;
			} else if (days.length < 5) {
				if (signDate == null) {
					logger.debug(rowNum + "行，无法处理的日期格式" + day);
					return null;
				}
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(signDate);

				year = calendar.get(Calendar.YEAR);

				month = Integer.parseInt(days[0]);
				d = Integer.parseInt(days[2]);
			} else {
				if (days[0].length() == 4) {
					year = Integer.parseInt(days[0]);
				} else {
					year = 2000 + Integer.parseInt(days[0]);
				}
				month = Integer.parseInt(days[2]);
				d = Integer.parseInt(days[4]);
			}
			String[] times = StringUtils.splitByCharacterTypeCamelCase(time);
			if (times.length < 7) {
				logger.debug(rowNum + "行，无法处理的日期时间格式" + day + " " + time);
				return null;
			}
			int h1 = Integer.parseInt(times[0]);
			int m1 = Integer.parseInt(times[2]);

			int h2 = Integer.parseInt(times[4]);
			int m2 = Integer.parseInt(times[6]);
			Calendar beginTime = Calendar.getInstance();
			beginTime.set(year, month - 1, d, h1, m1);

			Calendar endTime = Calendar.getInstance();
			endTime.set(year, month - 1, d, h2, m2);

			Foo f = new Foo();
			f.setBeginTime(beginTime.getTime());
			f.setEndTime(endTime.getTime());

			StringBuilder sb = new StringBuilder();
			for (int i = 7; i < times.length; i++) {
				String str = times[i];
				sb.append(str);
			}
			f.setTitle(sb.toString());

			return f;

		} catch (NumberFormatException ex) {
			logger.debug(rowNum + "行，无法处理的日期时间格式" + day + " " + time);
			return null;
		}
	}

	class Foo {
		Date beginTime;
		Date endTime;

		public Date getBeginTime() {
			return beginTime;
		}

		public void setBeginTime(Date beginTime) {
			this.beginTime = beginTime;
		}

		public Date getEndTime() {
			return endTime;
		}

		public void setEndTime(Date endTime) {
			this.endTime = endTime;
		}

		public String getTitle() {
			return title;
		}

		public void setTitle(String title) {
			this.title = title;
		}

		String title;
	}

	public ArrayList<Student> importSheet(Sheet sheet, int startIndex, int endIndex) throws IOException {
		Iterator<Row> rowIterator = sheet.rowIterator();

		ArrayList<Student> students = new ArrayList<Student>();

		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			int rowNum = row.getRowNum();

			if (rowNum > startIndex && rowNum < endIndex) {
				String name = getName(sheet, rowNum);
				String stuId = getStuId(sheet, rowNum);

				IDtoRow stuRow = null;
				if (StringUtils.isNotBlank(name)) {
					DtoTable table = jdbc.query("SELECT * FROM Student where name=?",
							JdbcTemplateHelper.getDtoTableExtractor(), name);

					if (table.size() == 1) {
						stuRow = table.get(0);
					} else if (table.size() > 1) {
						if (StringUtils.isNotBlank(stuId)) {
							stuRow = jdbc.query("SELECT * FROM Student where name=? AND (stuId = ? or title = ?)",
									JdbcTemplateHelper.getDtoRowMapper(), name, stuId, stuId);
						} else {
							stuRow = table.get(0);
						}
					} else {

					}
				} else {
					if (StringUtils.isNotBlank(stuId)) {
						DtoTable table = jdbc.query("SELECT * FROM Student where stuId=? or title=?",
								JdbcTemplateHelper.getDtoTableExtractor(), stuId, stuId);
						if (table.size() > 0) {
							stuRow = table.get(0);
						}
					}
				}

				if (stuRow != null) {
					Iterator<?> cellIterator = row.cellIterator();

					Student student = new Student();
					student.setId((String) stuRow.get("id"));

					SignCourse signCourse = new SignCourse();
					StringBuilder freezeLog = new StringBuilder();
					FreezeCourseLog freezeCourseLog = new FreezeCourseLog();
					int errorCount = 0;

					while (cellIterator.hasNext()) {
						Object obj = cellIterator.next();
						HSSFCell cell = (HSSFCell) obj;
						int columnIndex = cell.getColumnIndex();

						if (columnIndex == 0 || columnIndex == 1) {

						} else if (columnIndex == 2) {
							// 读取报名时间
							if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
								String source = cell.getStringCellValue();
								if (StringUtils.isNotBlank(source)) {
									source = source.trim();
									try {
										SimpleDateFormat df = new SimpleDateFormat("yyyy.MM.dd");
										Date resumeDate = df.parse(source);
										signCourse.setSignTime(resumeDate);
									} catch (ParseException ex) {
										SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
										try {
											Date resumeDate = df.parse(source);
											signCourse.setSignTime(resumeDate);
										} catch (ParseException e) {
											logger.debug(rowNum + "行，无法处理的日期：" + source);
										}
									}
								}
							} else {
								Date signTime = cell.getDateCellValue();
								signCourse.setSignTime(signTime);
							}

							if (signCourse.getSignTime() != null && signCourse.getSignTime().after(upper)) {
								logger.debug(rowNum + "行，报名日期超出范围");
								signCourse.setSignTime(null);
							}

						} else if (columnIndex == 3) {
							// 读取听课证号
							if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
								String lessonCard = cell.getStringCellValue().trim();
								if (lessonCard.length() > 20) {
									lessonCard = lessonCard.substring(0, 20);
								}
								signCourse.setLessonCard(lessonCard);
							} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
								logger.debug(rowNum + "行，无效的卡号:" + cell);
							} else {
								double d = cell.getNumericCellValue();
								DecimalFormat df = new DecimalFormat("0");

								String lessonCard = df.format(d);
								signCourse.setLessonCard(lessonCard);
							}
						} else if (columnIndex == 4) {
							// 读取卡号
							if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
								String cardId = cell.getStringCellValue().trim();
								if (cardId.length() > 20) {
									cardId = cardId.substring(0, 20);
								}
								signCourse.setCardId(cardId);
							} else {
								double d = cell.getNumericCellValue();
								DecimalFormat df = new DecimalFormat("0");

								String cardId = df.format(d);
								signCourse.setCardId(cardId);
							}
						} else if (columnIndex == 5) {
							if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
								logger.debug(rowNum + "行，无效的课程名称:" + cell);
							} else {
								StringBuilder sTitle = new StringBuilder();
								// 读取注册课程
								String courseName = cell.getStringCellValue();
								if (StringUtils.isBlank(courseName)) {
									Course course = findCourseByName("未知课程");
									signCourse.setCourse(course);
								} else {
									courseName = courseName.trim();
									Course course = findCourseByName(courseName);
									signCourse.setCourse(course);

									if (course == null) {
										Pattern pattern = Pattern.compile("^贵宾卡(\\d+)\\s*次\\s*$");
										Matcher matcher = pattern.matcher(courseName);

										if (matcher.find()) {
											String all = matcher.group(0);
											String t = matcher.group(1);
											System.out.println(t);

											int times = Integer.parseInt(t);
											signCourse.setTimes(times);

											course = findCourseByName("贵宾卡");
											signCourse.setCourse(course);

											sTitle.append(courseName);
										} else {
											// 尝试贵宾划次卡
											pattern = Pattern.compile("^贵宾划次卡(\\d+)\\s*次\\s*$");
											matcher = pattern.matcher(courseName);

											if (matcher.find()) {
												String all = matcher.group(0);
												String t = matcher.group(1);
												System.out.println(t);

												int times = Integer.parseInt(t);
												signCourse.setTimes(times);

												course = findCourseByName("贵宾卡");
												signCourse.setCourse(course);
												sTitle.append(courseName);
											} else {
												if (courseName.indexOf("雅思无限时") >= 0) {
													course = findCourseByName("雅思无限时");
													signCourse.setCourse(course);
													sTitle.append(courseName);
												} else if (courseName.indexOf("雅思无限思") >= 0) {
													course = findCourseByName("雅思无限时");
													signCourse.setCourse(course);
													sTitle.append(courseName);
												} else if (courseName.indexOf("沙龙自由人") >= 0) {
													course = findCourseByName("沙龙自由人");
													signCourse.setCourse(course);
													sTitle.append(courseName);
												} else if (courseName.indexOf("一对") >= 0) {
													course = findCourseByName("一对一");
													signCourse.setCourse(course);
													sTitle.append(courseName);
												} else if (courseName.indexOf("青少") >= 0
														|| courseName.indexOf("朗文") >= 0) {
													course = findCourseByName("少儿");
													signCourse.setCourse(course);
													sTitle.append(courseName);
												} else if (courseName.indexOf("雅思") >= 0) {
													course = findCourseByName("雅思无限时");
													signCourse.setCourse(course);
													sTitle.append(courseName);
												} else {
													logger.debug(rowNum + "行，课程名称:" + courseName + "无法找到");
													sTitle.append(courseName);
												}
											}
										}
									}
								}

								signCourse.setTitle(sTitle.toString());
							}
						} else if (columnIndex == 6) {
							// 读取期限
							if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
								double d = cell.getNumericCellValue();
								DecimalFormat df = new DecimalFormat("0");

								String term = df.format(d);
								signCourse.setCardId(term);
							} else {
								String term = cell.getStringCellValue().trim();
								signCourse.setTerm(term);
							}
						} else if (columnIndex == 7) {
							try {
								// 读取CC
								String ccName = cell.getStringCellValue();
								if (StringUtils.isNotBlank(ccName)) {
									User user = findCCByName(ccName.trim());
									if (user == null) {
										logger.debug(rowNum + "行，找不到名为" + ccName + "的CC");
									}
									student.setConsultor(user);
								}
							} catch (Exception ex) {

							}
						} else if (columnIndex == 8) {
							// 读取Tutor
							String tutorName = cell.getStringCellValue();
							if (StringUtils.isNotBlank(tutorName)) {
								User tutor = findTutorByName(tutorName.trim());
								if (tutor == null) {
									logger.debug(rowNum + "行，找不到名为" + tutorName + "的Tutor");
								}

								student.setTutor(tutor);
							}
						} else if (columnIndex == 9) {
							// 读取开课时间
							if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
								String source = cell.getStringCellValue();
								if (StringUtils.isNotBlank(source)) {
									source = source.trim();
									try {
										SimpleDateFormat df = new SimpleDateFormat("yyyy.MM.dd");
										Date beginTime = df.parse(source);
										signCourse.setBeginTime(beginTime);
									} catch (ParseException ex) {
										SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
										source = cell.getStringCellValue();

										try {
											Date beginTime = df.parse(source);
											signCourse.setBeginTime(beginTime);
										} catch (ParseException e) {
											logger.debug(rowNum + "行，无法处理的开课日期格式:" + source);
										}
									}
								}
							} else {
								Date beginTime = cell.getDateCellValue();
								signCourse.setBeginTime(beginTime);
							}

							if (signCourse.getBeginTime() != null && signCourse.getBeginTime().after(upper)) {
								logger.debug(rowNum + "行，课程开始时间超过当前时间10年");
								signCourse.setBeginTime(null);
							}
						} else if (columnIndex == 10) {
							// 读取结课时间
							if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
								String source = cell.getStringCellValue();
								if (StringUtils.isNotBlank(source)) {
									try {
										source = source.trim();
										SimpleDateFormat df = new SimpleDateFormat("yyyy.MM.dd");

										Date endTime = df.parse(source);
										signCourse.setEndTime(endTime);
									} catch (ParseException ex) {
										SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
										source = source.trim();
										try {
											Date endTime = df.parse(source);
											signCourse.setEndTime(endTime);
										} catch (ParseException e) {
											logger.debug(rowNum + "行，无法处理的日期格式：" + source);
										}
									}
								}
							} else {
								Date endTime = cell.getDateCellValue();
								signCourse.setEndTime(endTime);
							}

							if (signCourse.getEndTime() != null && signCourse.getEndTime().after(upper)) {
								logger.debug(rowNum + "行，报名日期超出范围");
								signCourse.setEndTime(null);
							}
						} else if (columnIndex == 11 || columnIndex == 12 || columnIndex == 13) {
							// 读取冻结概述
							if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
									|| cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
								double d = cell.getNumericCellValue();
								DecimalFormat df = new DecimalFormat("0");

								String title = df.format(d);
								freezeLog.append(title);
							} else {
								String title = cell.getStringCellValue().trim();
								freezeLog.append(title);
							}
						} else if (columnIndex == 14) {
							// 读取冻结后日期
							if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
								String source = cell.getStringCellValue();
								if (StringUtils.isNotBlank(source)) {
									try {
										source = source.trim();
										SimpleDateFormat df = new SimpleDateFormat("yyyy.MM.dd");
										Date resumeDate = df.parse(source);
										freezeCourseLog.setResumeDate(resumeDate);
									} catch (ParseException ex) {
										SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
										try {
											Date resumeDate = df.parse(source);
											freezeCourseLog.setEndDate(resumeDate);
										} catch (ParseException e) {
											logger.debug(rowNum + "行，错误的冻结后日期日期输入：" + source);
										}
									}
								}
							} else {
								Date resumeDate = cell.getDateCellValue();
								freezeCourseLog.setEndDate(resumeDate);
							}

							if (freezeCourseLog.getEndDate() != null) {
								if (freezeCourseLog.getEndDate().after(upper)) {
									logger.debug(rowNum + "行，冻结后结课日期过大：" + freezeCourseLog.getEndDate());
								}
							}
						} else if (columnIndex == 15) {
							// 读取课时
							if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
								String timesStr = cell.getStringCellValue();
								String[] timeStrs = StringUtils.splitByCharacterType(timesStr);
								try {
									int times = Integer.parseInt(timeStrs[0]);
									signCourse.setTimes(times);
								} catch (NumberFormatException e) {
									logger.debug(rowNum + "行，错误的课次输入：" + timesStr);
								}
							} else {
								double d = cell.getNumericCellValue();
								signCourse.setTimes((int) d);
							}
						} else {

							HSSFComment comment = cell.getCellComment();
							if (comment != null && comment.getString() != null) {

								String title = comment.getString().getString();

								BufferedReader br = new BufferedReader(new StringReader(title));
								// 计算机名
								String remark = br.readLine();
								if (remark != null) {
									// 日期
									String time = br.readLine();
									String day = time == null ? "" : time.trim();
									// 时间
									while (time != null) {
										time = br.readLine();
										if (time == null) {
											break;
										}
										time = time.trim();

										if (StringUtils.isNotBlank(time)) {
											time = time.trim();
											Date date = signCourse.getSignTime();
											if (date == null) {
												date = signCourse.getBeginTime();
												if (date == null) {
													date = signCourse.getEndTime();
												}
											}
											Foo f = parse(time, day, rowNum, date);
											if (f != null) {
												Date beginTime = f.getBeginTime();
												Date endTime = f.getEndTime();
												String t = f.getTitle().trim();

												if (beginTime.after(upper)) {
													logger.debug(rowNum + "行，日志开始时间过大");
												} else if (endTime.after(upper)) {
													logger.debug(rowNum + "行，日志结束时间过大");
												} else {
													OrderLessonLog orderLessonLog = new OrderLessonLog();
													orderLessonLog.setStudent(student);
													orderLessonLog.setBeginTime(beginTime);
													orderLessonLog.setCreateTime(new Date());
													orderLessonLog.setEndTime(endTime);
													orderLessonLog.setLogTime(beginTime);
													orderLessonLog.setSignCourse(signCourse);
													orderLessonLog.setTitle(remark + " " + t);
													orderLessonLog.setValid(true);

													student.getSchoolLogs().add(orderLessonLog);
												}
											}
										}
									}
								} else {
									logger.debug(rowNum + "行，日志无法读取");
								}
							} else {
								errorCount++;
								if (errorCount > 2) {
									errorCount = 0;
									break;
								}
							}

						}
					}

					if (freezeCourseLog != null) {
						student.getSchoolLogs().add(freezeCourseLog);
					}

					if (signCourse.getCourse() == null) {
						Course course = findCourseByName("未知课程");
						signCourse.setCourse(course);
					}

					student.getCourses().add(signCourse);

					students.add(student);
				}
			}
		}

		return students;
	}

	@Test
	@Transactional
	@Rollback(false)
	public void test1() throws IOException, ParseException {

		File file = new File("D:\\Desktop\\导入数据\\文汇校区学员学习记录.xls");

		FileInputStream fileInputStream = new FileInputStream(file);
		HSSFWorkbook book = new HSSFWorkbook(fileInputStream);

		HSSFSheet sheet = book.getSheetAt(6);

		ArrayList<Student> students = importSheet(sheet, 0, 70);

		for (Student student : students) {
			System.out.println(student);

			User cc = student.getConsultor();
			User tutor = student.getTutor();

			if (student.getConsultor() != null) {
				// 设置学员新的咨询师
				AppointConsultorLog appointConsultorLog = new AppointConsultorLog();
				appointConsultorLog.setConsultor(student.getConsultor());
				appointConsultorLog.setStudent(student);

				appointConsultorLog.setTitle("学习记录导入");

				appointConsultorLogRepository.save(appointConsultorLog);
			}
			if (student.getTutor() != null) {
				// 设置学员新的咨询师
				student.setTutor(tutor);

				AppointTutorLog appointTutorLog = new AppointTutorLog();
				appointTutorLog.setTutor(tutor);
				appointTutorLog.setStudent(student);

				appointTutorLog.setTitle("学习记录导入");

				appointTutorLogRepository.save(appointTutorLog);

			}
			for (SignCourse signCourse : student.getCourses()) {
				DtoTable table = null;
				String lessonCard = signCourse.getLessonCard();
				if (StringUtils.isBlank(lessonCard)) {
					table = jdbc.query("select * from signcourse where COURSE_ID =? AND STUDENT_ID =? AND ROwnum < 2",
							JdbcTemplateHelper.getDtoTableExtractor(), signCourse.getCourse().getId(), student.getId());
				} else {
					lessonCard = lessonCard.trim();
					table = jdbc
							.query("select * from signcourse where COURSE_ID =? AND STUDENT_ID =? AND LessonCard = ? AND ROwnum < 2",
									JdbcTemplateHelper.getDtoTableExtractor(), signCourse.getCourse().getId(),
									student.getId(), lessonCard);
				}
				if (table.size() > 0) {
					String signCourseId = (String) table.get(0).get("id");
					signCourse.setId(signCourseId);

				} else {
					String consultor = student.getConsultor() == null ? null : student.getConsultor().getId();
					signCourse = signCourseService.signUp(student.getId(), signCourse.getCourse().getId(), null,
							signCourse.getCardId(), signCourse.getLessonCard(), signCourse.getTitle(),
							signCourse.getTimes(), signCourse.getBeginTime(), signCourse.getEndTime(),
							signCourse.getSignTime(), consultor, null);
				}

				for (SchoolLog schoolLog : student.getSchoolLogs()) {
					if (schoolLog instanceof FreezeCourseLog) {
						FreezeCourseLog freezeCourseLog = (FreezeCourseLog) schoolLog;
						if (freezeCourseLog.getEndDate() != null) {
							signCourse.setLearnStatus(LearnStatus.Freeze);
							signCourse.setEndTime(freezeCourseLog.getEndDate());
							freezeCourseLog.setSignCourse(signCourse);
							freezeCourseLog.setStudent(student);
							freezeCourseLog.setResumeDate(null);

							freezeCourseLogRepository.save(freezeCourseLog);
						}
					}

					if (schoolLog instanceof OrderLessonLog) {
						OrderLessonLog orderLessonLog = (OrderLessonLog) schoolLog;
						if (orderLessonLog.getBeginTime() != null) {
							orderLessonLog.setSignCourse(signCourse);
							orderLessonLog.setStudent(student);

							orderLessonLogRepository.save(orderLessonLog);
						}
					}
				}
			}
		}
	}

	@Autowired
	FreezeCourseLogRepository freezeCourseLogRepository;

	@Autowired
	OrderLessonLogRepository orderLessonLogRepository;

	/**
	 * 获取合并单元格的值
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	public Cell getMergedRegionValue(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();

		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();

			if (row >= firstRow && row <= lastRow) {

				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);

					return fCell;
				}
			}
		}

		return null;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	public boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();

		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();

			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {

					return true;
				}
			}
		}

		return false;
	}

	String getName(Sheet sheet, int row) {
		int cloumn = 0;
		Cell cell = null;
		if (isMergedRegion(sheet, row, cloumn)) {
			cell = getMergedRegionValue(sheet, row, cloumn);
		} else {
			cell = sheet.getRow(row).getCell(cloumn);
		}
		if (cell == null) {
			return null;
		}

		// 读取名称
		String name = cell.getStringCellValue().trim();
		if (name != null) {
			name = name.trim();
		}

		return name;
	}

	String getStuId(Sheet sheet, int row) {
		int cloumn = 1;
		Cell cell = null;
		if (isMergedRegion(sheet, row, cloumn)) {
			cell = getMergedRegionValue(sheet, row, cloumn);
		} else {
			cell = sheet.getRow(row).getCell(cloumn);
		}
		if (cell == null) {
			return null;
		}
		String stuId = null;
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			stuId = cell.getStringCellValue();
			if (stuId.length() > 200) {
				stuId = stuId.substring(0, 20);
			}
		} else {
			double d = cell.getNumericCellValue();
			DecimalFormat df = new DecimalFormat("0");
			stuId = df.format(d);
			if (stuId.equals("0")) {
				stuId = "";
			}
		}
		if (stuId != null) {
			stuId = stuId.trim();
		}

		if (stuId.startsWith("9")) {
			stuId = ("0" + stuId);
		}

		return stuId;
	}

	@Autowired
	StudentRepository studentRepository;

	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;

	@Autowired
	JdbcTemplate jdbc;

	@Autowired
	AppointConsultorLogRepository appointConsultorLogRepository;

	@Autowired
	AppointTutorLogRepository appointTutorLogRepository;

	@Autowired
	UserRepository userRepository;

}